using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Stratman.Utilities;
using System.Diagnostics;

namespace Stratman.DesktopProcess.DatabaseSynchronizer
{
    /// <summary>
    /// Takes a directory of database scripts, builds a comparison database from them, and then
    /// updates the schema and data of a pre-existing target database so that the two are 
    /// equivalent.
    /// </summary>
    public class FilesToDatabaseProject : SynchronizerProject
    {
        /// <summary>
        /// Root directory from which we get the database scripts.
        /// </summary>
        protected string sourceDirectoryPath = "";
        /// <summary>
        /// Environment in which the synchronization operation is being run.
        /// </summary>
        protected string environment = "";
        /// <summary>
        /// Flag indicating whether we are to ignore errors in manual scripts.
        /// </summary>
        protected bool ignoreManualScriptErrors = false;

        /// <summary>
        /// Load the synchronization settings from the project file.
        /// </summary>
        /// <param name="projectFilePath">
        /// File from which we are to load settings.
        /// </param>
        /// <param name="commandLineArguments">
        /// Command line arguments that were specified.
        /// </param>
        protected override void LoadFromFile(string projectFilePath, string[] commandLineArguments)
        {
            // Instantiate the member variables from the command line arguments
            foreach (string argument in commandLineArguments)
            {
                if (argument.ToLower().StartsWith("/environment:"))
                    environment = argument.Substring(13);

                else if (argument.ToLower() == "/ignoremanualscripterrors")
                    ignoreManualScriptErrors = true;
            }

            if (String.IsNullOrEmpty(environment))
                throw new ArgumentException("For this project type, a value for the /environment parameter must be specified.");

            XmlDocument project = new XmlDocument();
            project.Load(projectFilePath);

            // Get the source directory and the standard synchronization options
            sourceDirectoryPath = XmlUtilities.GetSafeXmlValue(project, "/SynchronizerProject/Source/Directory", "");
            synchronizationOptions.Load(projectFilePath);

            synchronizationOptions.SourceServerName = synchronizationOptions.TargetServerName;
            synchronizationOptions.SourceUserName = synchronizationOptions.TargetUserName;
            synchronizationOptions.SourcePassword = synchronizationOptions.TargetPassword;

            synchronizationOptions.SetAllObjectTypes(true);
        }

        /// <summary>
        /// Build the comparison database from the given scripts and then update the schema and
        /// data of the target database so that the two are equivalent.
        /// </summary>
        public override void Execute()
        {
            // Create the comparison database
            Server targetServer = new Server(new ServerConnection(CreateConnection(synchronizationOptions.TargetServerName, synchronizationOptions.TargetDatabase, synchronizationOptions.TargetUserName, synchronizationOptions.TargetPassword)));
            Database comparisonDatabase = new Database(targetServer, Guid.NewGuid().ToString());
            ServerConnection targetConnection = null;
            bool backupNoChanges = noChanges;
            bool databaseCreated = false;

            TraceInformation("Creating comparison database ({0}.{1})", synchronizationOptions.TargetServerName, comparisonDatabase.Name);
            comparisonDatabase.Create();
            synchronizationOptions.SourceDatabase = comparisonDatabase.Name;

            try
            {
                noChanges = false;

                // Go through the script directories in a specific sequence so that we don't throw
                // any dependency errors when running the scripts
                ServerConnection comparisonConnection = new ServerConnection(CreateConnection(synchronizationOptions.TargetServerName, comparisonDatabase.Name, synchronizationOptions.TargetUserName, synchronizationOptions.TargetPassword));
                string[] directories = new string[] { "Users", "Roles", "User Defined Types", "Synonyms", "Assemblies", "Tables", "Static Tables", "Foreign Keys", "Views", "Functions", "Stored Procedures", "Triggers", "Rules", "Xml Schema Collections", "Message Types", "Contracts", "Queues", "Services", "Routes", "Event Notifications", "Service Bindings", "Certificates", "Symmetric Keys", "Asymmetric Keys" };

                try
                {
                    foreach (string directory in directories)
                    {
                        DirectoryInfo currentDirectory = new DirectoryInfo(sourceDirectoryPath + "\\" + directory);

                        if (currentDirectory.Exists)
                        {
                            databaseCreated = true;
                            TraceInformation("Creating {0}", directory.ToLower());

                            if (directory == "Views")
                            {
                                List<FileInfo> failedScripts = new List<FileInfo>();

                                // Iterate through the list of views and record which scripts fail
                                foreach (FileInfo file in currentDirectory.GetFiles())
                                {
                                    try
                                    {
                                        TraceVerbose("Creating {0} {1}", StringUtilities.Singularize(file.Directory.Name).ToLower(), file.Name.Substring(0, file.Name.Length - 4));
                                        RunSqlScript(file.FullName, comparisonConnection);
                                    }

                                    catch (ExecutionFailureException exception)
                                    {
                                        string warningTrap = exception.Message;
                                        failedScripts.Add(file);
                                    }
                                }

                                // If we have failed scripts, re-run them
                                if (failedScripts.Count > 0)
                                {
                                    TraceWarning("Retrying {0} failed view scripts", failedScripts.Count);

                                    int previousNumErrors = failedScripts.Count;

                                    // Loop through the failed scripts and try to run them again;
                                    // keep going until either all the scripts succeed or, on a
                                    // pass through the failed scripts, no more succeed
                                    do
                                    {
                                        previousNumErrors = failedScripts.Count;

                                        for (int i = 0; i < failedScripts.Count; i++)
                                        {
                                            try
                                            {
                                                // Run the script and remove it from the list if it
                                                // succeeds
                                                RunSqlScript(failedScripts[i].FullName, comparisonConnection);
                                                failedScripts.RemoveAt(i);
                                                i--;
                                            }

                                            catch (ExecutionFailureException exception)
                                            {
                                                string warningTrap = exception.Message;
                                            }
                                        }
                                    }
                                    while (failedScripts.Count < previousNumErrors);

                                    // If we still have failed scripts in the list, it means that
                                    // we got "stuck" and weren't able to create all of the views
                                    // successfully
                                    if (failedScripts.Count > 0)
                                        throw new ExecutionFailureException("Unable to create views successfully");
                                }
                            }

                            else
                            {
                                foreach (FileInfo file in currentDirectory.GetFiles())
                                {
                                    if (directory == "Static Tables")
                                    {
                                        TraceVerbose("Adding data to table {0}", file.Name.Substring(0, file.Name.Length - 4));
                                        synchronizationOptions.StaticTables.Add(GetFullyQualifiedName(file.Name));
                                    }

                                    else
                                        TraceVerbose("Creating {0} {1}", StringUtilities.Singularize(file.Directory.Name).ToLower(), file.Name.Substring(0, file.Name.Length - 4));

                                    // Run the specified object script
                                    RunSqlScript(file.FullName, comparisonConnection);
                                }
                            }
                        }
                    }

                    if (!databaseCreated)
                        throw new ArgumentException(String.Format("The directory {0} did not contain any subdirectories for creating the comparison database.", sourceDirectoryPath));

                    // Run the manual scripts against the source database
                    TraceInformation("Running pre-synchronization scripts against the source database");
                    RunManualScripts(sourceDirectoryPath + "\\Manual Scripts\\Source\\All Environments", comparisonConnection);
                    RunManualScripts(sourceDirectoryPath + "\\Manual Scripts\\Source\\" + environment, comparisonConnection);

                    noChanges = backupNoChanges;

                    // Run the pre-synchronization manual scripts agains the target database
                    targetConnection = new ServerConnection(CreateConnection(synchronizationOptions.TargetServerName, synchronizationOptions.TargetDatabase, synchronizationOptions.TargetUserName, synchronizationOptions.TargetPassword));

                    try
                    {
                        TraceInformation("Running pre-synchronization scripts against the target database");
                        RunManualScripts(sourceDirectoryPath + "\\Manual Scripts\\Target\\Before Synchronization\\All Environments", targetConnection, true);
                        RunManualScripts(sourceDirectoryPath + "\\Manual Scripts\\Target\\Before Synchronization\\" + environment, targetConnection, true);
                    }

                    finally
                    {
                        targetConnection.Disconnect();
                    }
                }

                catch (SqlException exception)
                {
                    TraceCritical("Database error occurred while constructing the comparison database: {0}", (exception.InnerException != null ? exception.InnerException.Message : exception.Message));
                    throw;
                }

                catch (ExecutionFailureException exception)
                {
                    TraceCritical("Database error occurred while constructing the comparison database: {0}", (exception.InnerException != null ? exception.InnerException.Message : exception.Message));
                    throw;
                }

                catch (Exception exception)
                {
                    TraceCritical("Error occurred while constructing the comparison database: {0}", exception.Message);
                    throw;
                }

                finally
                {
                    comparisonConnection.Disconnect();
                    noChanges = backupNoChanges;
                }

                // Call the generic method to synchronize the target database to the comparison
                // database
                SynchronizeTwoDatabases(synchronizationOptions);
                    
                // Run the post-synchronization manual scripts against the target database
                targetConnection = new ServerConnection(CreateConnection(synchronizationOptions.TargetServerName, synchronizationOptions.TargetDatabase, synchronizationOptions.TargetUserName, synchronizationOptions.TargetPassword));

                try
                {
                    TraceInformation("Running post-synchronization scripts against the target database");
                    RunManualScripts(sourceDirectoryPath + "\\Manual Scripts\\Target\\After Synchronization\\All Environments", targetConnection, true);
                    RunManualScripts(sourceDirectoryPath + "\\Manual Scripts\\Target\\After Synchronization\\" + environment, targetConnection, true);
                }

                catch (SqlException exception)
                {
                    TraceCritical("Database error occurred while running post-synchronization scripts: {0}", (exception.InnerException != null ? exception.InnerException.Message : exception.Message));
                    throw;
                }

                catch (ExecutionFailureException exception)
                {
                    TraceCritical("Database error occurred while running post-synchronization scripts: {0}", (exception.InnerException != null ? exception.InnerException.Message : exception.Message));
                    throw;
                }

                catch (Exception exception)
                {
                    TraceCritical("Database error occurred while running post-synchronization scripts: {0}", exception.Message);
                    throw;
                }

                finally
                {
                    targetConnection.Disconnect();
                }
            }

            finally
            {
                // Drop the comparison database
                TraceInformation("Dropping the comparison database ({0}.{1})", synchronizationOptions.SourceServerName, synchronizationOptions.SourceDatabase);
                targetServer.KillDatabase(comparisonDatabase.Name);
            }
        }

        /// <summary>
        /// Run the manual scripts in a particular directory using a given connection.
        /// </summary>
        /// <param name="directoryPath">
        /// Directory containing the scripts that we're to run.
        /// </param>
        /// <param name="connection">
        /// Connection to use when running the scripts.
        /// </param>
        protected void RunManualScripts(string directoryPath, ServerConnection connection)
        {
            RunManualScripts(directoryPath, connection, false);
        }

        /// <summary>
        /// Run the manual scripts in a particular directory using a given connection.
        /// </summary>
        /// <param name="directoryPath">
        /// Directory containing the scripts that we're to run.
        /// </param>
        /// <param name="connection">
        /// Connection to use when running the scripts.
        /// </param>
        /// <param name="useTransaction">
        /// Flag indicating whether or not the running of these manual scripts should be protected
        /// by a transaction that can be rolled back in case of errors.
        /// </param>
        protected void RunManualScripts(string directoryPath, ServerConnection connection, bool useTransaction)
        {
            if (!Directory.Exists(directoryPath))
                return;

            try
            {
                // Start the transaction, if necessary, and run the scripts
                if (useTransaction)
                    connection.BeginTransaction();

                RunScriptsInDirectory(directoryPath, connection);

                // Commit the transaction if necessary
                if (useTransaction)
                    connection.CommitTransaction();
            }

            catch (ExecutionFailureException exception)
            {
                // If we aren't ignoring manual script errors, re-throw this exception
                if (!ignoreManualScriptErrors)
                    throw;

                // Otherwise, just write a message to the log
                else
                    TraceError("Database error occurred while constructing the comparison database: {0}", (exception.InnerException != null ? exception.InnerException.Message : exception.Message));
            }

            finally
            {
                // If we're still in a transaction (indicating that we're not ignoring errors and
                // we encountered one), roll it back
                if (connection.TransactionDepth > 0)
                    connection.RollBackTransaction();
            }
        }

        /// <summary>
        /// Run scripts in a directory and its subdirectories using a given connection.
        /// </summary>
        /// <param name="directoryPath">
        /// Root directory containing the scripts that we're to run.
        /// </param>
        /// <param name="connection">
        /// Connection to use when running the scripts.
        /// </param>
        protected void RunScriptsInDirectory(string directoryPath, ServerConnection connection)
        {
            // Run each script in the directory
            foreach (string fileName in Directory.GetFiles(directoryPath, "*.sql"))
            {
                TraceVerbose("Running manual script {0}", fileName);
                RunSqlScript(fileName, connection);
            }

            // Recurse into the child directories and do the same
            foreach (string childDirectory in Directory.GetDirectories(directoryPath))
                RunScriptsInDirectory(childDirectory, connection);
        }
    }
}
